<?php
	define('CONFIG_FILE',true);
	include '../config.php';
	
	define('DB_FILE',true);
	require_once '../Tool/DB/SqlDBManager.class.php';

	session_start();
	if (empty($_SESSION["userType"])) {
		$url="../Index.php";
		include_once '../Tool/Common/Redirect.php';
		exit();
	}
	
	$sqlDBManager = new SqlDBManager();
	
	$centreName = $_SESSION["centreName"]; 
	$userType = $_SESSION["userType"]; 

	$page = isset($_POST['page']) ? intval($_POST['page']) : 1;
	$rows = isset($_POST['rows']) ? intval($_POST['rows']) : 10;
	$sort = isset($_POST['sort']) ? strval($_POST['sort']) : 'NRIC';
	$order = isset($_POST['order']) ? strval($_POST['order']) : 'asc';
	$searchid='';
	if(isset($_POST['searchid'])){
		$searchid = $_POST['searchid'];
	}  
	$offset = ($page-1)*$rows;
	$result = array();
	
	//Filter the $searchid
	$searchid=addslashes($searchid);
	$searchid=str_replace("%", "\%", $searchid);
	$searchid=str_replace("_", "\_", $searchid);

	if($userType=="System Admin"){
		$sql = "SELECT count(*)
				FROM attendancerecord, client, centre 
				WHERE DATE(SignInTimeStamp)=CURDATE() 
				AND attendancerecord.NRIC = client.NRIC 
				AND attendancerecord.CentreId = centre.CentreId
				AND(attendancerecord.NRIC like ?
				OR client.CustomerName like ?
				OR client.Gender like ?
				OR client.Daycare like ?
				OR attendancerecord.SignInTimeStamp like ?
				OR centre.CentreName like ?)";

		$parameters=array("%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%");
		$res=$sqlDBManager->queryRow($sql,$parameters);
	}else{
		$sql = "SELECT count(*)
				FROM attendancerecord, client, centre 
				WHERE DATE(SignInTimeStamp)=CURDATE() 
				AND attendancerecord.NRIC = client.NRIC 
				AND attendancerecord.CentreId = centre.CentreId
				And centre.CentreName=?
				AND (attendancerecord.NRIC like ?
				OR client.CustomerName like ?
				OR client.Gender like ?
				OR client.Daycare like ?
				OR attendancerecord.SignInTimeStamp like ?
				OR centre.CentreName like ?)";
		$parameters=array($centreName,"%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%");
		$res=$sqlDBManager->queryRow($sql,$parameters);
	}
	if(!empty($res)){
		$result["total"] = $res[0];
	}

	if($userType=="System Admin"){
		$sql = "SELECT attendancerecord.NRIC, client.CustomerName, 
			client.Gender, TIMESTAMPDIFF(YEAR,client.DateOfBirth,CURDATE()) AS Age, client.Daycare, 
			centre.CentreName, attendancerecord.SignInTimeStamp
			FROM attendancerecord, client, centre 
			WHERE DATE(SignInTimeStamp)=CURDATE() 
			AND attendancerecord.NRIC = client.NRIC 
			AND attendancerecord.CentreId = centre.CentreId
			AND (attendancerecord.NRIC like ?
			OR client.CustomerName like ?
			OR client.Gender like ?
			OR client.Daycare like ?
			OR attendancerecord.SignInTimeStamp like ?
			OR centre.CentreName like ?) 
			order by $sort $order
			limit $offset,$rows";
		$parameters=array("%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%");
		$res=$sqlDBManager->queryRows($sql,$parameters);
	}else{
		$sql = "SELECT attendancerecord.NRIC, client.CustomerName, 
			client.Gender, TIMESTAMPDIFF(YEAR,client.DateOfBirth,CURDATE()) AS Age, client.Daycare, 
			centre.CentreName, attendancerecord.SignInTimeStamp
			FROM attendancerecord, client, centre 
			WHERE DATE(SignInTimeStamp)=CURDATE() 
			AND attendancerecord.NRIC = client.NRIC 
			AND attendancerecord.CentreId = centre.CentreId
			And centre.CentreName=? 
			AND (attendancerecord.NRIC like ?
			OR client.CustomerName like ?
			OR client.Gender like ?
			OR client.Daycare like ?
			OR attendancerecord.SignInTimeStamp like ?
			OR centre.CentreName like ?) 
			order by $sort $order
			limit $offset,$rows";

		$parameters=array($centreName,"%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%","%".$searchid."%");
		$res=$sqlDBManager->queryRows($sql,$parameters);
	}

	$items = array();
		
	if(!empty($res)){
		for($i=0;$i<count($res);$i++){
			array_push($items, $res[$i]);
		}	
	}

	$activityList="";
	if(!empty($res)){
		for($i=0;$i<count($res);$i++){
			$NRIC=$res[$i]['NRIC'];
			$sql="SELECT activity.ActivityName
				FROM activity, activityrecord
				WHERE activity.ActivityId = activityrecord.ActivityId
				AND activityrecord.NRIC = ? 
				AND date(activityrecord.Date)=CURDATE()";
			$parameters=array($NRIC);
			$res2=$sqlDBManager->queryRows($sql,$parameters);
			
			$activityList="";
			if(!empty($res2)){
				for($j=0;$j<count($res2);$j++){
					if($j!=count($res2)-1){
						$activityList .=$res2[$j]['ActivityName'].",";
					}else{
						$activityList .=$res2[$j]['ActivityName'];
					}
				}
			}else{
				$activityList="No Activities Participated";
			}
			$items[$i]['ActivityList']=$activityList;
			$items[$i]['7']=$activityList;
		}
	}

	
	//close connection
	$sqlDBManager->close_connect();
	
	$result["rows"] = $items;
	echo json_encode($result);
?>
